﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Data.OleDb;
using System.Data.OracleClient;
using Dominio;

namespace Persistencia
{
    public class ProfessorDAO
    {
        protected int _count = 0;
        protected SqlConnection conn = null;
        protected void closeConexao()
        {
            GerenciadorConexao.closeConexao(conn);
            conn = null;
        }
        protected void abrirConexao()
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
        }
        public int selectCountAll()
        {
            return _count;
        }

        #region métodos auxiliares
        private String getNomeTabela()
        {
            return "Professor";
        }
        private String getSelect()
        {
            return "select * from " + this.getNomeTabela();
        }

        protected DbParameter novoParametro(String nome, DbType tipo, Object valor)
        {
            SqlParameter par = new SqlParameter();
            par.ParameterName = nome;
            if (tipo != null)
                par.DbType = tipo;
            if (valor != null)
                par.Value = valor;
            return par;

        }
        private ProfessorVO getEntidade(DbDataReader dr)
        {
            ProfessorVO professor = new ProfessorVO();

            try
            {
                professor.id = (int)dr["id"];
                professor.Nome = (string)dr["nome"].ToString().Trim();
                professor.Cpf = (string)dr["cpf"].ToString().Trim();
                professor.Dnascimento = (DateTime)dr["dnascimento"];
                professor.Email = (string)dr["email"].ToString().Trim();
                professor.Endereco = (string)dr["endereco"].ToString().Trim();
                professor.Matricula = (string)dr["matricula"].ToString().Trim();
                professor.Rg = (string)dr["rg"].ToString().Trim();
                professor.Senha = (string)dr["senha"].ToString().Trim();
                professor.Telefone = (string)dr["telefone"].ToString().Trim();
                professor.Sexo = (char)Convert.ToChar(dr["sexo"]);


            }
            catch (Exception)
            {
            }
            return professor;
        }
        #endregion

        #region métodos acessadores
        public IList<ProfessorVO> list(string nome, string matricula)
        {
           
                return this.recuperarProfessor(nome, matricula);
        }
        /// <summary>
        /// Exibe todos os objetos cadastrados.
        /// </summary>
        /// <returns>Retorna a lista de objetos da classe T.</returns>
       
        private List<ProfessorVO> recuperarProfessor(string nome, string matricula)
        {
            List<ProfessorVO> professor = new List<ProfessorVO>();

            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand(this.getSelect() + " where nome LIKE '%' + @parNome + '%' and matricula LIKE '%' + @parMatricula + '%' ORDER BY nome", conn);
            string _nome = nome;
            string _matricula = matricula;
            if (_nome == null) _nome = "";
            if (_matricula == null) _matricula = "";

            abrirConexao();
            try
            {
                command.Parameters.Add(this.novoParametro("@parNome", DbType.String, _nome));
                command.Parameters.Add(this.novoParametro("@parMatricula", DbType.String, _matricula));
                DbDataReader dr = command.ExecuteReader();
                try
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            professor.Add(this.getEntidade(dr));
                        }
                    }
                }
                finally { dr.Close(); }
            }
            finally { closeConexao(); }

            return professor;
        }
        public ProfessorVO findByPrimaryKey(int id)
        {
            //Activator.CreateInstance(
            ProfessorVO vo = new ProfessorVO();
            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand(this.getSelect() + " where id = @parID", conn);

            abrirConexao();
            try
            {
                command.Parameters.Add(this.novoParametro("parID", DbType.Int32, id));
                DbDataReader dr = command.ExecuteReader();
                try
                {
                    if (dr.HasRows)
                    {
                        dr.Read();
                        vo = this.getEntidade(dr);
                    }
                }
                finally { dr.Close(); }
            }
            finally { closeConexao(); }

            return vo;
        }

        public int ObterID(string matricula)
        {
            ProfessorVO professor = new ProfessorVO();
            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand(" Select ID from "+getNomeTabela() + " where matricula LIKE @matricula", conn);
            abrirConexao();
            try
            {
                
                command.Parameters.Add(this.novoParametro("@matricula", DbType.String, matricula));
                DbDataReader dr = command.ExecuteReader();
                try
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            professor = this.getEntidade(dr);
                        }
                    }
                }
                finally { dr.Close(); }
            }
            finally { closeConexao(); }

            return professor.id;
        }

        #endregion

        #region métodos modificadores
        public int inserir(ProfessorVO vo)
        {
            int retorno = 0;

            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand();
            command.CommandText = "INSERT INTO " + this.getNomeTabela() + " (matricula, nome, sexo, endereco, telefone, cpf, rg, dnascimento, email, senha) VALUES (@matricula, @nome, @sexo, @endereco, @telefone, @cpf, @rg, @dnascimento, @email, @senha) ";
            command.Connection = conn;

            command.Parameters.Add(this.novoParametro("matricula", DbType.String, vo.Matricula));
            command.Parameters.Add(this.novoParametro("nome", DbType.String, vo.Nome));
            command.Parameters.Add(this.novoParametro("sexo", DbType.String, vo.Sexo));
            command.Parameters.Add(this.novoParametro("endereco", DbType.String, vo.Endereco));
            command.Parameters.Add(this.novoParametro("telefone", DbType.String, vo.Telefone));
            command.Parameters.Add(this.novoParametro("cpf", DbType.String, vo.Cpf));
            command.Parameters.Add(this.novoParametro("rg", DbType.String, vo.Rg));
            command.Parameters.Add(this.novoParametro("dnascimento", DbType.Date, vo.Dnascimento));
            command.Parameters.Add(this.novoParametro("email", DbType.String, vo.Email));
            command.Parameters.Add(this.novoParametro("senha", DbType.String, vo.Senha));

            try
            {
                abrirConexao();

                retorno = command.ExecuteNonQuery();
                if (retorno > 0)
                    vo.id = GerenciadorConexao.getId(this.getNomeTabela());
                return retorno;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConexao();
            }
        }
        public virtual int alterar(ProfessorVO vo)
        {
            int retorno = 0;

            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE " + this.getNomeTabela() + " set matricula = @matricula, nome = @nome, sexo = @sexo, endereco = @endereco, telefone = @telefone, cpf = @cpf, rg = @rg, dnascimento = @dnascimento, email = @email where id = @id";
            command.Connection = conn;

            command.Parameters.Add(this.novoParametro("matricula", DbType.String, vo.Matricula));
            command.Parameters.Add(this.novoParametro("nome", DbType.String, vo.Nome));
            command.Parameters.Add(this.novoParametro("sexo", DbType.String, vo.Sexo));
            command.Parameters.Add(this.novoParametro("endereco", DbType.String, vo.Endereco));
            command.Parameters.Add(this.novoParametro("telefone", DbType.String, vo.Telefone));
            command.Parameters.Add(this.novoParametro("cpf", DbType.String, vo.Cpf));
            command.Parameters.Add(this.novoParametro("rg", DbType.String, vo.Rg));
            command.Parameters.Add(this.novoParametro("dnascimento", DbType.Date, vo.Dnascimento));
            command.Parameters.Add(this.novoParametro("email", DbType.String, vo.Email));
            command.Parameters.Add(this.novoParametro("@id", DbType.Int32, vo.id));
            try
            {
                abrirConexao();
                retorno = command.ExecuteNonQuery();
                return retorno;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConexao();
            }
        }
        public virtual int alterarSenha(ProfessorVO vo)
        {
            int retorno = 0;

            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE " + this.getNomeTabela() + " set senha = @senha where id = @id";
            command.Connection = conn;

            command.Parameters.Add(this.novoParametro("senha", DbType.String, vo.Senha));
            command.Parameters.Add(this.novoParametro("@id", DbType.Int32, vo.id));

            try
            {
                abrirConexao();
                retorno = command.ExecuteNonQuery();
                return retorno;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConexao();
            }
        }

        
        #endregion
    }
}